﻿Imports MySql.Data.MySqlClient
Imports System.Threading
Imports Microsoft.Office.Interop
Public Class frmedit_container
    Dim mySqlCommand As New MySqlCommand
    Dim mySqlAdaptor As New MySqlDataAdapter
    Dim mySqlReader As MySqlDataReader
    Dim respone As Object
    Dim mysql As MySqlConnection = main_form.mysqlconection
    Public Delegate Sub DelegateSub(ByVal x As Integer)
    Dim pathExcel As String
    Private Sub frmedit_container_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
        System.Threading.Thread.CurrentThread.CurrentUICulture = System.Threading.Thread.CurrentThread.CurrentCulture
        mysql.Close()

        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If

        MySqlCommand.CommandText = "Select  CTNMAINID,CTNSTRING,CTNAGENT,CTNCONSI,VOYDATEEN,CTNSIZE,CTNVOYN from ctnmain join voyage on ctnmain.CTNVOYN = voyage.VOYAGEID  where CTNSTAT= '1' order by CTNMAINID ASC;"
        ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

        MySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = MySqlCommand
        Try
            mySqlReader = MySqlCommand.ExecuteReader

            While (mySqlReader.Read())

                With ListView1.Items.Add(mySqlReader("CTNMAINID"))
                    .SubItems.Add(mySqlReader("CTNSTRING"))
                    .SubItems.Add(mySqlReader("CTNAGENT"))
                    .SubItems.Add(mySqlReader("CTNCONSI"))

                    .SubItems.Add(mySqlReader("CTNSIZE"))


                    .SubItems.Add("N" + Format(mySqlReader("CTNVOYN"), "000"))
                End With

            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()
    End Sub
    Public Sub searchctn()
        mysql.Close()

        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If

        mySqlCommand.CommandText = "Select  CTNMAINID,CTNSTRING,CTNAGENT,CTNCONSI,CTNSIZE,CTNVOYN from ctnmain    where CTNSTAT= '1' and(  CTNAGENT like '%" & TextBoxItem2.Text & "%' or CTNVOYN like '%" & TextBoxItem2.Text & "%'  or CTNSTRING ='%" & TextBoxItem2.Text & "%')  order by CTNMAINID ASC;"
        ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand
        Try
            mySqlReader = mySqlCommand.ExecuteReader
            ListView1.Items.Clear()

            While (mySqlReader.Read())

                With ListView1.Items.Add(mySqlReader("CTNMAINID"))
                    .SubItems.Add(mySqlReader("CTNSTRING"))
                    .SubItems.Add(mySqlReader("CTNAGENT"))
                    .SubItems.Add(mySqlReader("CTNCONSI"))

                    .SubItems.Add(mySqlReader("CTNSIZE"))


                    .SubItems.Add("N" + Format(mySqlReader("CTNVOYN"), "000"))
                End With

            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()


    End Sub

    Private Sub ListView1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListView1.Click



        Label5.Text = ListView1.SelectedItems(0).SubItems(0).Text
        TextBox1.Text = ListView1.SelectedItems(0).SubItems(1).Text
        TextBox2.Text = ListView1.SelectedItems(0).SubItems(2).Text
        ComboBox1.Text = ListView1.SelectedItems(0).SubItems(4).Text




    End Sub
    Public Sub clearCTN()

        Label5.Text = ""
        TextBox1.Text = ""
        TextBox2.Text = ""
        ComboBox1.Text = ""
        searchctn()
    End Sub
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click




        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If
        Dim commandText3 As String
        commandText3 = "UPDATE ctnmain SET CTNSTRING = '" & TextBox1.Text & "' , CTNAGENT ='" & TextBox2.Text & "', CTNSIZE= '" & MySqlHelper.EscapeString(ComboBox1.Text) & "'  where CTNMAINID = '" & Label5.Text & "'; "


        Try
            mySqlCommand.CommandText = commandText3
            mySqlCommand.CommandType = CommandType.Text
            mySqlCommand.Connection = mysql

            mySqlCommand.ExecuteNonQuery()

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()


        clearCTN()

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        searchctn()
    End Sub

    Private Sub TextBoxItem2_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles TextBoxItem2.KeyDown
        If e.KeyCode = Keys.Enter Then
            searchctn()
        End If
    End Sub

    Private Sub ButtonX1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonX1.Click
        Dim msg As New Form1
        Dim result = msg.ShowDialog()
        If result = Windows.Forms.DialogResult.Yes Then
            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If
            Try

                mySqlCommand.CommandText = "DELETE FROM ctnmain where CTNMAINID = '" & Label5.Text & "';"
                mySqlCommand.CommandType = CommandType.Text
                mySqlCommand.Connection = mysql

                mySqlCommand.ExecuteNonQuery()
                mysql.Close()
            Catch ex As Exception

                MsgBox(ex.ToString)
                Exit Sub
            End Try
            searchctn()


            'user clicked "go"
        ElseIf result = Windows.Forms.DialogResult.No Then

            'user clicked "pause"
        Else
            'user closed the window without clicking a button
        End If
    End Sub
End Class